# coding=UTF-8
# Copyright (c) 2025 Arfa Digital Consulting
# This program and the accompanying materials
# are made available under the terms of the Eclipse Public License 2.0
# which accompanies this distribution, and is available at
# https://www.eclipse.org/legal/epl-2.0/
#
# SPDX-License-Identifier: EPL-2.0
#
# Contributors:
#    Benjamin Arfa - initial API and implementation
#

import csv
from datetime import datetime
from django.utils import timezone
from django.core.exceptions import ObjectDoesNotExist
from pybirdai.models.bird_meta_data_model import (
    CUBE_LINK,
    CUBE_STRUCTURE_ITEM_LINK,
    MAINTENANCE_AGENCY,
    CUBE,
    CUBE_STRUCTURE_ITEM
)

class ImporterJoins:

    @staticmethod
    def _get_instance_or_skip(model, pk_value, row_id, field_name):
        """Looks up FK instance, returns instance or None if pk_value is empty or object not found.
           Prints error message for critical missing items and signals caller to skip."""
        if not pk_value:
            print(f"Error: Missing required {model.__name__} pk for {row_id} related to field '{field_name}'. Skipping row.")
            return None
        try:
            if model == CUBE_STRUCTURE_ITEM:
                return model.objects.get(cube_variable_code=pk_value)
            return model.objects.get(pk=pk_value)
        except ObjectDoesNotExist:
            print(f"Error: {model.__name__} with pk '{pk_value}' not found for {row_id} related to field '{field_name}'. Skipping row.")
            return None

    @staticmethod
    def _get_instance_or_none(model, pk_value, row_id, field_name):
        """Looks up FK instance, returns instance or None if pk_value is empty or object not found.
           Prints warning for non-critical missing items."""
        if not pk_value:
            return None # Field is optional, missing PK is fine
        try:
            return model.objects.get(pk=pk_value)
        except ObjectDoesNotExist:
            print(f"Warning: {model.__name__} with pk '{pk_value}' not found for {row_id} related to field '{field_name}'. Setting FK to None.")
            return None

    @staticmethod
    def _parse_date_or_none(date_str, row_id, field_name):
        """Parses ISO date string, returns datetime object or None on error or empty string."""
        if not date_str:
            return None
        try:
            dt_obj = datetime.fromisoformat(date_str)
            return timezone.make_aware(dt_obj) if timezone.is_naive(dt_obj) else dt_obj
        except ValueError:
            print(f"Warning: Invalid date format for {field_name} '{date_str}' for {row_id}. Setting field to None.")
            return None

    @staticmethod
    def handle(input_path:str="resources/joins_export/export_file.csv"):
        """
        Imports CUBE_LINK and CUBE_STRUCTURE_ITEM_LINK data from CSV files
        generated by the export process.

        Args:
            input_path: The base path used to generate the CSV file names.
                        Defaults to "resources/joins_export/export_file.csv".
                        The actual files imported will be
                        input_path.replace(".csv", "cube_link.csv") and
                        input_path.replace(".csv", "cube_structure_item_link.csv").
        """
        cube_link_path = input_path.replace(".csv", "cube_link.csv")
        cube_structure_item_link_path = input_path.replace(".csv", "cube_structure_item_link.csv")

        print("Starting import process...")

        # --- Import CUBE_LINK ---
        print(f"\nImporting CUBE_LINK from {cube_link_path}")
        imported_cube_links_count = 0

        try:
            with open(cube_link_path, 'r', newline='', encoding='utf-8') as csvfile:
                reader = csv.DictReader(csvfile)
                if not reader.fieldnames:
                    print("Warning: CUBE_LINK CSV file is empty or has no headers.")
                    # Continue to try the second file
                else: # Only process if there are fieldnames
                    for row in reader:
                        link_id = row.get("CUBE_LINK_ID")
                        if not link_id:
                            print(f"Warning: Skipping CUBE_LINK row with no CUBE_LINK_ID: {row}")
                            continue

                        try: # Keep a general try/except for unexpected row processing errors
                            # Prepare data dictionary for update_or_create defaults
                            link_defaults = {
                                "code": row.get("CODE"),
                                "name": row.get("NAME"),
                                "description": row.get("DESCRIPTION"),
                                "version": row.get("VERSION"),
                                "order_relevance": row.get("ORDER_RELEVANCE") if isinstance(row.get("ORDER_RELEVANCE"),int) else 0, # Assuming model field handles type conversion
                                "cube_link_type": row.get("CUBE_LINK_TYPE"),
                                "join_identifier": row.get("JOIN_IDENTIFIER"),
                            }

                            # Handle FK lookups using helpers
                            link_defaults["maintenance_agency_id"] = ImporterJoins._get_instance_or_none(
                                MAINTENANCE_AGENCY, row.get("MAINTENANCE_AGENCY_ID"), f"CUBE_LINK '{link_id}'", "MAINTENANCE_AGENCY_ID"
                            )

                            primary_cube = ImporterJoins._get_instance_or_skip(
                                CUBE, row.get("PRIMARY_CUBE_ID"), f"CUBE_LINK '{link_id}'", "PRIMARY_CUBE_ID"
                            )
                            if primary_cube is None:
                                continue # Skip row if mandatory FK not found/missing

                            foreign_cube = ImporterJoins._get_instance_or_skip(
                                CUBE, row.get("FOREIGN_CUBE_ID"), f"CUBE_LINK '{link_id}'", "FOREIGN_CUBE_ID"
                            )
                            if foreign_cube is None:
                                continue # Skip row if mandatory FK not found/missing

                            link_defaults["primary_cube_id"] = primary_cube
                            link_defaults["foreign_cube_id"] = foreign_cube

                            # Handle dates using helper
                            link_defaults["valid_from"] = ImporterJoins._parse_date_or_none(
                                row.get("VALID_FROM"), f"CUBE_LINK '{link_id}'", "VALID_FROM"
                            )
                            link_defaults["valid_to"] = ImporterJoins._parse_date_or_none(
                                row.get("VALID_TO"), f"CUBE_LINK '{link_id}'", "VALID_TO"
                            )

                            # Use update_or_create based on CUBE_LINK_ID
                            obj, created = CUBE_LINK.objects.update_or_create(
                                cube_link_id=link_id,
                                defaults=link_defaults
                            )
                            imported_cube_links_count += 1
                            # print(f"{'Created' if created else 'Updated'} CUBE_LINK: {obj.cube_link_id}") # Uncomment for verbose output

                        except Exception as e:
                            print(f"Error processing CUBE_LINK row with ID '{link_id}': {e}")
                            # Continue to the next row

        except FileNotFoundError:
            print(f"Error: CUBE_LINK file not found at {cube_link_path}. Skipping CUBE_LINK import.")
        except Exception as e:
            print(f"Error reading CUBE_LINK file {cube_link_path}: {e}")


        print(f"Finished importing {imported_cube_links_count} CUBE_LINK records.")

        # --- Import CUBE_STRUCTURE_ITEM_LINK ---
        print(f"\nImporting CUBE_STRUCTURE_ITEM_LINK from {cube_structure_item_link_path}")
        imported_item_links_count = 0

        try:
            with open(cube_structure_item_link_path, 'r', newline='', encoding='utf-8') as csvfile:
                reader = csv.DictReader(csvfile)
                if not reader.fieldnames:
                    print("Warning: CUBE_STRUCTURE_ITEM_LINK CSV file is empty or has no headers.")
                    # Don't return here
                else: # Only process if there are fieldnames
                    for row in reader:
                        item_link_id = row.get("CUBE_STRUCTURE_ITEM_LINK_ID")
                        if not item_link_id:
                            print(f"Warning: Skipping CUBE_STRUCTURE_ITEM_LINK row with no CUBE_STRUCTURE_ITEM_LINK_ID: {row}")
                            continue

                        try: # Keep a general try/except for unexpected row processing errors
                            # Prepare data dictionary for update_or_create defaults
                            item_link_defaults = {}

                            # Handle FK lookups using helper
                            cube_link = ImporterJoins._get_instance_or_skip(
                                CUBE_LINK, row.get("CUBE_LINK_ID"), f"CUBE_STRUCTURE_ITEM_LINK '{item_link_id}'", "CUBE_LINK_ID"
                            )
                            if cube_link is None:
                                continue # Skip row if mandatory FK not found/missing

                            foreign_variable = ImporterJoins._get_instance_or_skip(
                                CUBE_STRUCTURE_ITEM, row.get("FOREIGN_CUBE_VARIABLE_CODE"), f"CUBE_STRUCTURE_ITEM_LINK '{item_link_id}'", "FOREIGN_CUBE_VARIABLE_CODE"
                            )
                            if foreign_variable is None:
                                continue # Skip row if mandatory FK not found/missing

                            primary_variable = ImporterJoins._get_instance_or_skip(
                                CUBE_STRUCTURE_ITEM, row.get("PRIMARY_CUBE_VARIABLE_CODE"), f"CUBE_STRUCTURE_ITEM_LINK '{item_link_id}'", "PRIMARY_CUBE_VARIABLE_CODE"
                            )
                            if primary_variable is None:
                                continue # Skip row if mandatory FK not found/missing

                            item_link_defaults["cube_link_id"] = cube_link
                            item_link_defaults["foreign_cube_variable_code"] = foreign_variable
                            item_link_defaults["primary_cube_variable_code"] = primary_variable


                            # Use update_or_create based on CUBE_STRUCTURE_ITEM_LINK_ID
                            obj, created = CUBE_STRUCTURE_ITEM_LINK.objects.update_or_create(
                                cube_structure_item_link_id=item_link_id,
                                defaults=item_link_defaults
                            )
                            imported_item_links_count += 1
                            # print(f"{'Created' if created else 'Updated'} CUBE_STRUCTURE_ITEM_LINK: {obj.cube_structure_item_link_id}") # Uncomment for verbose output

                        except Exception as e:
                            print(f"Error processing CUBE_STRUCTURE_ITEM_LINK row with ID '{item_link_id}': {e}")
                            # Continue to the next row

        except FileNotFoundError:
            print(f"Error: CUBE_STRUCTURE_ITEM_LINK file not found at {cube_structure_item_link_path}. Skipping CUBE_STRUCTURE_ITEM_LINK import.")
        except Exception as e:
            print(f"Error reading CUBE_STRUCTURE_ITEM_LINK file {cube_structure_item_link_path}: {e}")


        print(f"Finished importing {imported_item_links_count} CUBE_STRUCTURE_ITEM_LINK records.")
        print("\nImport process finished.")
